﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class ConnectDB
    'Private connString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=JessupDB;Integrated Security=True"
    Shared connString As String = "Data Source=HOANGVUKENSHIN;Initial Catalog=HTDNLMT;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False"

    Private conn As SqlConnection
    Private cmd As SqlCommand

    Public Function Connect() As Boolean
        conn = New SqlConnection(connString)
        Try
            conn.Open()
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show("Connnection failed. View Details " + ex.Message)

        End Try
        Return True
    End Function
    Public Sub Close()
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
        conn.Dispose()
    End Sub

    Public Function Execute(ByVal query As String) ' INSERT/UPDTE/DELETE
        cmd = New SqlCommand()
        Connect()
        cmd.Connection = conn
        cmd.CommandText = query
        Return cmd.ExecuteNonQuery()
    End Function
    Public Function GetData(ByVal strsql As String) As DataTable
        cmd = New SqlCommand()
        Connect()
        cmd.Connection = conn
        cmd.CommandText = strsql

        Dim dr As SqlDataReader = cmd.ExecuteReader()
        Dim dt As New DataTable
        dt.Load(dr)
        Close()
        Return dt
    End Function

    Public Sub LoadComboListbox(ByVal cb As Object, ByVal sql As String, ByVal fieldname As String)
        Dim dt As DataTable
        Dim ketnoi = New ConnectDB()
        dt = ketnoi.GetData(sql)

        For i As Integer = 0 To dt.Rows.Count() - 1
            cb.Items.Add(dt.Rows(i)(fieldname).ToString)
        Next

    End Sub

End Class
